import threading,time,pymysql,datetime,xlwt,os,re
import tkinter as tk
import tkinter.font as font
from tkinter import messagebox


class DB:
    def db1(self,host,username,password,port):
        port=int(port)
        try:
            self.db=pymysql.connect(host=host, user=username, password=password, port=port)
            self.cursor=self.db.cursor()
            sql="show databases;"
            self.cursor.execute(sql)
            data=self.cursor.fetchall()
            self.cursor.close()
            self.db.close()
            return data
        except Exception as e:
            messagebox.showerror('error',str(e))

    def searcetable(self,host,username,password,port,name):
        port = int(port)
        try:
            self.db = pymysql.connect(host=host, user=username, password=password, port=port)
            self.cursor = self.db.cursor()
            sql=f"use {name};"
            sql1='show tables;'
            self.cursor.execute(sql)
            self.cursor.execute(sql1)
            data=self.cursor.fetchall()
            return data
        except Exception as e:
            messagebox.showerror('error',e)
        finally:
            self.cursor.close()
            self.db.close()

    def select(self,host,username,password,port,sql,sql1):
        port = int(port)
        sql=sql
        try:
            self.db = pymysql.connect(host=host, user=username, password=password, port=port)
            self.cursor = self.db.cursor()
            self.cursor.execute(sql)
            self.cursor.execute(sql1)
            data = self.cursor.fetchall()
            self.db.commit()
            return data
        except Exception as e:
            messagebox.showerror('error',e)
        finally:
            self.cursor.close()
            self.db.close()


class GUI:
    dataname="mysql"
    def __init__(self):
        self.dataname=None
        self.root=tk.Tk()
        self.root.title('DB')
        self.root.geometry("1350x800")
        self.root.resizable(False,False)
        self.la1=tk.Label(text='host',width=20)
        self.la1.grid(row=0,column=0)
        self.en1=tk.Entry(width=20)
        self.en1.grid(row=0,column=1)
        self.la2 = tk.Label(text='username', width=20)
        self.la2.grid(row=0, column=2)
        self.en2 = tk.Entry(width=20)
        self.en2.grid(row=0, column=3)
        self.la3 = tk.Label(text='password', width=20)
        self.la3.grid(row=0, column=4)
        self.en3 = tk.Entry(width=20,show="*")
        self.en3.grid(row=0, column=5)
        self.la4 = tk.Label(text='port', width=20)
        self.la4.grid(row=0, column=6)
        self.en4 = tk.Entry(width=20)
        self.en4.grid(row=0, column=7)
        self.button1=tk.Button(width=20,command=self.huoquku,text="连接DB")
        self.button1.grid(row=0,column=8)
        self.la11 = tk.Label(text='查询语句', width=25)
        self.la11.grid(row=1, column=0)
        self.txt3 = tk.Text(width=140,height=12)
        self.txt3.grid(row=1, column=1, columnspan=7)
        self.button2 = tk.Button(width=20,height=9, command=self.select, text="开始执行")
        self.button2.grid(row=1, column=8)
        self.la5 = tk.Label(text='库名', width=40)
        self.la5.grid(row=2, column=0,columnspan=2)
        self.la6 = tk.Label( width=20,text='表名')
        self.la6.grid(row=2, column=2,columnspan=1)
        self.la7 = tk.Label(width=20, text='查询结果')
        self.la7.grid(row=2, column=3, columnspan=6)
        self.txt1=tk.Text(width=20,height=35)
        self.txt1.grid(row=3,column=2,columnspan=1,rowspan=22)
        self.txt2 = tk.Text(width=120,height=35)
        self.txt2.grid(row=3, column=3, columnspan=6, rowspan=22)
        self.la8 = tk.Label(self.root, text="", width=20)
        self.la8.grid(row=25, column=0)
        t=threading.Thread(target=self.date)
        self.button5 = tk.Button(width=20,command=self.bigselect, text="查询结果放大显示")
        self.button5.grid(row=25, column=8)
        self.button6 = tk.Button(width=20, command=self.sizeselect, text="查询结果缩小显示")
        self.button6.grid(row=25, column=7)
        self.button7 = tk.Button(width=20, command=self.savexls, text="保存结果至xls")
        self.button7.grid(row=25, column=6)
        t.start()
        self.root.mainloop()

    def savexls(self):
        data = self.txt2.get('1.0', tk.END)
        t=threading.Thread(target=Savexls().savexls,args=(data,))
        t.start()

    def bigselect(self):
        self.txt2.configure(width=190,height=40)
        self.txt2.grid(row=0,rowspan=25,column=0,columnspan=9)
    def sizeselect(self):
        data=self.txt2.get('1.0',tk.END)
        self.txt2.grid_remove()
        self.txt2 = tk.Text(width=120)
        self.txt2.grid(row=3, column=3, columnspan=6, rowspan=22)
        self.txt2.insert(tk.END,data)
    def date(self):
         while True:
            shijian = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
            self.la8.configure(text=shijian)
            time.sleep(1)

    def conndb(self):
        try:
            host = self.en1.get().split()
            username = self.en2.get().split()
            password = self.en3.get().split()
            port = self.en4.get().split()
            return host,username,password,port
        except Exception as e:
            messagebox.showerror('error',e)
    def huoquku(self):
        host, username, password, port=self.conndb()
        try:
            dalist=DB().db1(host[0],username[0],password[0],port[0])
            for i in range(0,len(dalist)):
                if i<=14:
                    self.i=tk.Button(self.root,text=dalist[i],width=20,command=lambda b=dalist[i]: self.huoqubiao(b))
                    self.i.grid(row=i+3,column=0)
                elif 14<i<=29:
                    self.i = tk.Button(self.root, text=dalist[i], width=20,command=lambda b=dalist[i]: self.huoqubiao(b))
                    self.i.grid(row=i + 3-15, column=1)
            self.root.title(f"{host[0]}已连接")
        except Exception as e:
            messagebox.showerror('error','请输入完整信息')

    def huoqubiao(self,name):
        host, username, password, port=self.conndb()
        try:
            name=name[0]
            self.dataname=name
            self.la5.configure(text=f'当前连接{self.dataname}库')
            data=DB().searcetable(host[0],username[0],password[0],port[0],name)
            self.txt1.delete('1.0',tk.END)
            for i in range(0,len(data)):
                self.txt1.insert(tk.END,f"{data[i][0]}\n\n")
        except Exception as e:
            messagebox.showerror('error',e)

    def select(self):
        host, username, password, port=self.conndb()
        self.txt2.delete('1.0', tk.END)
        try:
            if self.dataname:
                sql = f"use {self.dataname};"
            else:
                sql = 'use mysql;'
            sql1=self.txt3.get(1.0,tk.END).strip()
            sqltxt=sql1.split(' ')
            sqltxt1=sqltxt[1].split(',')
            for i in sqltxt1:
                self.txt2.insert(tk.END,f'{i}\t')
            self.txt2.insert(tk.END, '\n')
            data=DB().select(host[0],username[0],password[0],port[0],sql,sql1)
            for i in data:
                for ii in i:
                    self.txt2.insert(tk.END,f'{ii}\t')
                self.txt2.insert(tk.END,'\n')
        except Exception as e:
            messagebox.showerror('error',e)

class  Savexls:
    def savexls(self,data):
        path=os.getcwd()
        data=data.split('\n')
        savetime=datetime.datetime.now().strftime("%Y-%m-%d-%H-%M-%S")
        save=xlwt.Workbook(encoding='utf-8')
        sheet=save.add_sheet('sheet1',cell_overwrite_ok=True)
        for x in range(0,len(data)):
            datali=data[x].split('\t')
            for y in range(0,len(datali)):
                sheet.write(x,y,datali[y])
        save.save(rf'{path}\{savetime}.xls')

if __name__=='__main__':
    GUI()